﻿Imports Microsoft.VisualBasic

Public Class locations

    'membros ou campos da tabela types
    Public id_location As Integer
    Public name As String
    Public description As String
    Public id_image As Integer
    Public belongs_to As Integer

    'Nascimento da variavel, ou seja o construtor
    Public Sub New()
        id_location = 0
        name = ""
        description = ""
        id_image = 0
        belongs_to = 0
    End Sub

    Public Property name_propriedade() As String
        Get
            Return name
        End Get
        Set(ByVal value As String)
            name = value
        End Set
    End Property
    'função para inserir os valores da base de dados
    Function insert() As String 'se inseriu bem ou não

        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim sqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        sqlConnection1.ConnectionString = connection_string
        sqlConnection1.Open()

        Dim sqlcomand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim String_sql As String = "INSERT INTO locations(name,description,id_image,belongs_to) VALUEs( '" + name + "', '" + description + "'," + id_image.ToString() + ", " + belongs_to.ToString() + ")"

        'associa o comando a executar com a conexão
        sqlcomand1.Connection = sqlConnection1

        'definir o sql que o comando vai executar
        sqlcomand1.CommandText = String_sql

        'Executa do comando na base de dados base de dados
        sqlcomand1.ExecuteNonQuery()

        'Fecha a ligação á base de dados
        sqlConnection1.Close()

        Return String_sql
    End Function

    'funçãoo para alterar os valores da base de dados
    Function update() As String 'se inseriu bem ou não

        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim sqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        sqlConnection1.ConnectionString = connection_string
        sqlConnection1.Open()

        Dim sqlcomand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim String_sql As String = "update locations set name='" + name + "',description='" + description + "',id_image=" + id_image.ToString() + ",belongs_to=" + belongs_to.ToString() + " where id_location=" + id_location.ToString()

        Return String_sql

        'associa o comando a executar com a conexão
        sqlcomand1.Connection = sqlConnection1

        'definir o sql que o comando vai executar
        sqlcomand1.CommandText = String_sql

        'Executa do comando na base de dados base de dados
        sqlcomand1.ExecuteNonQuery()

        'Fecha a ligação á base de dados
        sqlConnection1.Close()


    End Function

    'função para elemina os valores da base de dados
    Function delet() As String 'se inseriu bem ou não

        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

        Dim sqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        sqlConnection1.ConnectionString = connection_string
        sqlConnection1.Open()

        Dim sqlcomand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()

        Dim String_delet As String = "delete from locations where id_location = " + id_location.ToString()

        'associa o comando a executar com a conexão
        sqlcomand1.Connection = sqlConnection1

        'definir o sql que o comando vai executar
        sqlcomand1.CommandText = String_delet

        'Executa do comando na base de dados base de dados
        sqlcomand1.ExecuteNonQuery()

        'Fecha a ligação á base de dados
        sqlConnection1.Close()

        id_location = 0
        name = ""
        description = ""
        id_image = 0
        belongs_to = 0

        Return String_delet
    End Function

    Function select_type(ByVal sql As String) As String
        Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
        Dim sqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
        sqlConnection1.ConnectionString = connection_string
        sqlConnection1.Open()
        Dim sqlcomand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
        sqlcomand1.Connection = sqlConnection1
        sqlcomand1.CommandText = sql

        Dim sqlDataReader1 As System.Data.SqlClient.SqlDataReader
        sqlDataReader1 = sqlcomand1.ExecuteReader()
        If sqlDataReader1.Read() Then
            id_location = sqlDataReader1.GetInt32(0)
            name = sqlDataReader1.GetString(1)
            description = sqlDataReader1.GetString(2)
            id_image = sqlDataReader1.GetInt32(3)
            belongs_to = sqlDataReader1.GetInt32(4)

        End If

        sqlDataReader1.Close()
        sqlConnection1.Close()

        Return sql
    End Function
    Function first() As String
        Dim sql As String = "SELECT top 1 id_location,name,description,id_image,belongs_to from locations ORDER BY id_location ASC"

        Return select_type(sql)
    End Function

    Function last() As String
        Dim sql As String = "SELECT top 1 id_location,name,description,id_image,belongs_to from locations ORDER BY id_location DESC"


        Return select_type(sql)
    End Function

    Function mnext() As String
        Dim sql As String = "SELECT top 1 id_location,name,description,id_image,belongs_to from locations where id_location > " + id_location.ToString() + " order by id_location asc;"

        Return select_type(sql)
    End Function

    Function previous() As String
        Dim sql As String = "SELECT top 1 id_location,name,description,id_image,belongs_to from locations where id_location < " + id_location.ToString() + " order by id_location desc;"

        Return select_type(sql)
    End Function

    Function select_location_by_id() As String
        Dim sql As String = "SELECT id_location,name,description,id_image,belongs_to from locations where id_location = " + id_location.ToString()

        Return select_type(sql)
    End Function
End Class